from pyspark import SparkContext, SparkConf
from pyspark.sql import SparkSession
from google.cloud import storage
from pyspark.sql.functions import lit
from functools import reduce
from pyspark.sql import DataFrame
from pyspark.sql.functions import *
from datetime import datetime
from pyspark.ml import Pipeline
from pyspark.sql.window import Window
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
spark = SparkSession.builder.appName('Nifty50').getOrCreate()
#change configuration settings on Spark
/gateway/default/node/conf?host&port = spark.sparkContext._conf.setAll([('spark.executor.memory', '30g'), ('spark.app.name', 'Spark Updated Conf'), ('spark.executor.cores', '4'), ('spark.cores.max', '4'), ('spark.driver.memory','30g'), ("spark.driver.maxResultSize", "70g")])
gcs_client = storage.Client()
bucket_name = 'bigdata_nifty50'
bucket = gcs_client.bucket(bucket_name)
file_name = 'df_with_indicators.csv'
df = spark.read.csv('gs://{}//{}'.format(bucket_name, file_name), inferSchema=True)
new_columns = ['date',
'close',
'high',
'low',
'open',
'volume',
'sma5',
'sma10',
'sma15',
'sma20',
'ema5',
'ema10',
'ema15',
'ema20',
'upperband',
'middleband',
'lowerband',
'HT_TRENDLINE',
'KAMA10',
'KAMA20',
'KAMA30',
'SAR',
'TRIMA5',
'TRIMA10',
'TRIMA20',
'ADX5',
'ADX10',
'ADX20',
'APO',
'CCI5',
'CCI10',
'CCI15',
'macd510',
'macd520',
'macd1020',
'macd1520',
'macd1226',
'MFI',
'MOM10',
'MOM15',
'MOM20',
'ROC5',
'ROC10',
'ROC20',
'PPO',
'RSI14',
'RSI8',
'slowk',
'slowd',
'fastk',
'fastd',
'fastksr',
'fastdsr',
'ULTOSC',
'WILLR',
'ATR',
'Trange',
'TYPPRICE',
'HT_DCPERIOD',
'BETA',
'sector',
'company',
'Inflation Rate ',
'Balance of Trade',
'Bank Lending Rate',
'Car Registrations',
'Consumer Price Index',
'Crude Oil Production',
'Fiscal Expenditure',
'Industrial Production',
'Food Inflation',
'Producer Prices',
'Reverse Repo Rate',
'Steel Production',
'Tourist Arrivals',
'Corporate Tax Rate ',
'Export Prices',
'GDP per Capita PPP',
'GDP',
'Gross National Product',
'Import Prices',
'Military Expenditure']
from functools import reduce
old_columns = df.schema.names
df = reduce(lambda data, idx: data.withColumnRenamed(old_columns[idx], new_columns[idx]), range(len(old_columns)), df)
from pyspark.sql.types import DateType
def get_month(date):
return datetime.strptime('{}-{}'.format(date.year, date.month), '%Y-%m')
udf_get_month = udf(get_month, DateType())
df = df.withColumn("month", udf_get_month("date"))
cols = cols = df.columns
cols.remove('month')
exprs = {x: "avg" for x in cols}
exprs["sector"] = "first"
monthly_df = df.groupby(df.company, df.month).agg(exprs)
monthly_df.show()
22/11/27 04:41:18 WARN org.apache.spark.sql.catalyst.util.package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'. [Stage 5:> (0 + 1) / 1]
+--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+ | company| month|avg(Reverse Repo Rate)| avg(RSI14)|avg(Car Registrations)| avg(CCI10)| avg(TRIMA10)| avg(SAR)| avg(fastdsr)| avg(ADX20)| avg(WILLR)| avg(sma10)| avg(macd1226)| avg(sma20)| avg(ema15)| avg(ema10)| avg(KAMA10)| avg(TRIMA20)|avg(Tourist Arrivals)| avg(fastksr)|avg(Steel Production)|avg(Gross National Product)| avg(TRIMA5)| avg(macd1020)| avg(CCI5)| avg(MOM15)|avg(Export Prices)| avg(TYPPRICE)|avg(GDP per Capita PPP)| avg(HT_DCPERIOD)| avg(ema5)| first(sector)| avg(open)|avg(Producer Prices)| avg(PPO)| avg(ADX10)| avg(CCI15)|avg(Fiscal Expenditure)|avg(company)|avg(Import Prices)| avg(ATR)|avg(Crude Oil Production)| avg(upperband)| avg(low)| avg(BETA)| avg(ROC20)| avg(macd520)| avg(date)| avg(MOM10)| avg(fastk)| avg(MFI)|avg(Corporate Tax Rate )| avg(APO)| avg(sma5)| avg(KAMA20)| avg(RSI8)| avg(macd510)| avg(lowerband)|avg(Military Expenditure)| avg(sma15)| avg(fastd)| avg(ADX5)| avg(slowd)| avg(ema20)|avg(Consumer Price Index)|avg(Industrial Production)| avg(KAMA30)| avg(GDP)|avg(Bank Lending Rate)| avg(Trange)| avg(MOM20)|avg(Balance of Trade)| avg(HT_TRENDLINE)| avg(ULTOSC)|avg(Inflation Rate )| avg(ROC5)| avg(close)|avg(Food Inflation)| avg(middleband)| avg(ROC10)| avg(volume)| avg(macd1520)| avg(high)| avg(slowk)| +--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+ | ACC|2015-02-01| 6.75|51.341732681694985| 225854.0| 7.375884452695391|1587.8424552911074|1510.2454148176594|50.706838008648184|20.462641615692533| -46.29977340716897| 1587.84245999731| 0.12230880288822872|1587.7528822105724|1587.7986367188514| 1587.843024086454| 1587.733592170198|1587.7521971835392| 761000.0| 50.70507114232472| 7201.0| 1.1234571E7| 1587.888222552665| 0.08816524997553743| 4.343301891589986| 0.27004168347451923|108.59999999998561|1587.9311572766767| 5464.390000000442| 21.87608712444841|1587.8882100745654|Construction Mate...|1587.9137891622913| 109.59999999998553|0.007674402115951372| 29.21212900844431| 9.028998534078514| 1459536.0| null| 99.80000000000787| 1.8585889236980633| 766.0| 1590.021200626735|1587.0337299986616|0.42110396048628823|0.023059009707359655| 0.1333513889255957|1.4240168706897943E9| 0.1828492671776254| 51.7909829704862| 51.2160368938616| 34.60999999999342| 0.12360637043529334|1587.8881807180298|1587.5488107685205| 51.38818083882827| 0.04518598810631182|1585.7551608093222| 51295.5|1587.7972255837933| 51.79274715544397| 41.21381507022425|51.788739949990934| 1587.754858685636| 119.69999999998569| 3.0| 1587.552765320451| 2103.589999999876| 10.25| 1.859378781766871| 0.35625924431894607| -6.739999999999756|1587.7401894560496| 49.72602358398766| 5.370000000000197|0.005904407062116368|1587.9246201425278| 6.879999999999271|1587.8881807180298|0.011816810643922928|1479.1233024068845| 0.04377308750693913|1588.8351216888473| 51.79274715544397| | ACC|2015-03-01| 6.5| 49.26022045204308| 230638.0| -2.8599664582717432| 1607.493176269166|1481.6999999999937| 49.45097333766073|20.107719707337395| -51.84631725334683|1607.4931658478522|-0.11862338746655288|1607.5820377400717|1607.5359058034214|1607.4921798563203|1607.4641189255476|1607.5830542584379| 729000.0| 49.4612167279632| 7797.0| 1.1234571E7|1607.4470481351766|-0.08631317568049836| -1.2101921282049783|-0.26939109721601917|108.59999999998657| 1607.411927447776| 5464.390000000426| 21.32974276022784|1607.4470878096097|Construction Mate...|1607.3934196814046| 109.90000000001356|-0.00750501397769...| 28.06320578513418|-3.9322704019188452| 1644761.0| null| 99.80000000000372| 1.7998194401569139| 776.0|1609.6666933640868|1606.6252568110745| 0.4323121683588249|-0.02070900759244595|-0.13140522239365465| 1.426726133514962E9|-0.18334822093196373| 48.19587039651099| 49.38956894040469| 34.60999999999421|-0.12173804124953139|1607.4471147833701| 1607.513997478999| 49.20891400913171|-0.04509204671315628|1605.2275362026492| 51295.5| 1607.538175276659| 48.19506185678671| 39.73838296008299| 48.19867963414227|1607.5784930320058| 120.199999999989| 2.399999999999745|1607.6570956204644|2103.5899999998787| 10.25| 1.798987643293147| -0.3529477445288059| -11.40000000000004|1607.6378757152445| 48.77194431854942| 5.254000000000607|-0.00540608338244...| 1607.410495757038| 6.139999999999562|1607.4471147833701|-0.01077843245633034| 835.1360726514813|-0.04258722857502069|1608.2000297752038| 48.19506185678671| | ACC|2015-04-01| 6.5|49.165088984825665| 217437.0| -4.717163141905119|1562.5383974628887|1527.6033562702564| 49.96701556540131|22.761689668246436|-51.788364958816985|1562.5385676169037| -0.1239612690578514| 1562.627546692905|1562.5841517839124|1562.5398489241945|1562.7110371633155|1562.6262911234785| 542000.0|49.960885162402924| 7514.0| 1.1234571E7| 1562.496399557727|-0.08875125884670691| -2.1594415982654236| -0.2625052661143098| 108.599999999986|1562.4842274025164| 5464.390000000436|21.614185750228994|1562.4965920333618|Construction Mate...| 1562.51197865469| 110.19999999999004|-0.00846665811432...| 30.37985532023296| -7.215517161280854| 154617.0| null| 99.80000000000615| 1.9424694415940356| 751.0|1564.7294651732652|1561.5625052661155| 0.4021367915220439|-0.02286424541595063|-0.13200814967075009| 1.429287499059121E9|-0.17022187894958574|48.231160638320205| 49.27533764552744| 34.60999999999375| -0.1268438151507352|1562.4963684875615|1562.9364669444374| 49.11761080437563|-0.04325689082404319| 1560.263271801844| 51295.5|1562.5824280297707| 48.22986357345047| 41.76106004606293| 48.22586271149177|1562.6286001830333| 120.69999999998684| 0.6999999999999061|1563.0103126896392|2103.5899999998774| 10.25| 1.9433295885409507|-0.35611571408510057| -10.990000000000293| 1562.680559951745| 48.22026794874327| 4.865000000000557|-0.00532738576378714|1562.4630740064679| 5.110000000000419|1562.4963684875615|-0.01086939164072...| 944.0188175818004|-0.04444839911823...| 1563.427102934988| 48.22986357345047| | ACC|2015-06-01| 6.25| 50.06128006567195| 212847.0|-7.75786530244087E-4|1429.0924965656582| 1772.0|47.988246422126444|23.702557066748952| -53.35532370286863|1429.0924406060544|-0.05907494546064...|1429.1366242424435|1429.1138203394376| 1429.09228279734|1429.2044854742376|1429.1368674929893| 512000.0| 47.99632723020713| 7514.0| 1.1234571E7| 1429.07022830244|-0.04267913256888...| -0.7005825671741872|-0.13282424242424207|108.59999999998473|1429.0738787878784| 5464.390000000458| 20.28382016989599| 1429.070247298425|Construction Mate...|1429.0462727272716| 111.80000000001543|-0.00407150819038...| 31.38094241014406| 0.8357921298311575| 430993.0| null| 99.80000000001168| 1.5126217016854038| 771.0|1430.8166914929384|1428.3552666666694|0.34080710970597405|-0.01113917075913...|-0.06471463148516812|1.4343669927272727E9|-0.08886060606060607| 46.11344568660276| 48.66066086447552| 34.60999999999269|-0.06029871795062927|1429.0702715151297|1429.2662578887755| 49.98127632091758|-0.02203549891628...|1427.3238515373225| 51295.5| 1429.114791111119| 46.10906663778272| 42.73896372753478| 46.1090329139597|1429.1349619299078| 123.0| 1.0|1429.3638357069417|2103.5899999998737| 10.0| 1.5101878787878926|-0.17333939393939424| -10.830000000001412|1429.1331846194385| 47.09547363258592| 5.398000000000537|-0.00282347749615...|1429.0525999999963| 5.480000000000606|1429.0702715151297|-0.00568135229469...| 749.5726060606061|-0.02114159046882...|1429.8137696969682| 46.10906663778272| | ACC|2015-11-01| 5.75| 49.6973343879359| 228267.0| -1.3283054924466138| 1358.833029423867| 1772.0|49.597447397153616|23.574440858798727| -51.8408827313687|1358.8333585370108|-0.04416919149467...|1358.8648953754487|1358.8494992079802| 1358.833937012956| 1358.890330580212| 1358.864555445262| 816000.0| 49.59326975136052| 7263.0| 1.1234571E7|1358.8183704712112|-0.03131466133702789|-0.44825301592624606|-0.09223668014770532|108.59999999998834|1358.8143221382084| 5464.390000000396|21.233387765941437| 1358.818878091093|Construction Mate...| 1358.827650782485| 109.9000000000118|-0.00325060486522...|31.963244821006764|-1.8921506604650362| 1142307.0| null| 99.79999999999596| 1.214172333493394| 756.0| 1360.207310256967|1358.2256901705546| 0.3148661440308871|-0.00848063450218...|-0.04637358319764...|1.4475296107227008E9|-0.05960963601195714|49.100664550345535| 48.48326063588668| 34.60999999999569|-0.04434985548089865|1358.8184121680852|1358.8941399064222|49.666260100339265|-0.01505892186062...|1357.4295140791935| 51295.5|1358.8490481214476| 49.09851793156933| 44.14007261180953| 49.08991943512114|1358.8652516742977| 126.5999999999867| 0.8000000000000854|1358.9134984691475|2103.5899999998837| 9.699999999999124| 1.210858097415161|-0.12310532794091794| -9.779999999999264|1358.8360764114282| 49.39287127639707| 5.412000000000216|-0.00191690917479...|1358.8077633198518| 6.0699999999997525|1358.8184121680852|-0.00409852301958...| 561.7714084754704|-0.01575246631427...|1359.4095129242182| 49.09851793156933| | ACC|2016-09-01| 6.0| 49.47981740709897| 268058.0| -3.355108467975603| 1640.078506222307| 1772.0| 49.32462591776426|22.145909080051588| -49.24277370804279|1640.0787773333143|-0.10157107240626705| 1640.15184600001|1640.1154056167984| 1640.079088598737|1640.0507933909203|1640.1518331513332| 608000.0| 49.33331776178084| 7863.0| 1.2163619E7|1640.0425680102508|-0.07260925776571409| -2.392483115568347| -0.2167933333333333| 104.0|1639.9916177777811| 5851.390000000454|20.553903875878117|1640.0429674306301|Construction Mate...|1640.0316866666642| 111.40000000001469|-0.00615900945273...|30.793453611784884|-3.5730626901496163| 1027728.0| null| 84.5| 1.52610906918264| 725.0|1641.7572504287605| 1639.233913333328| 0.3494186581532837|-0.01693074618504...| -0.1087304258575334| 1.47409332E9|-0.14448666666666665| 51.34402173623531| 49.69582880509122| 34.609999999993356|-0.10199867518521963| 1640.042665333331|1640.0675511171312| 49.46088070897399|-0.03612116809181932|1638.3280802379052| 56637.60000000612|1640.1154520000027| 51.33785857627501| 42.90342496766221| 51.3322719745169|1640.1516978565016| 130.90000000001598| 5.0|1640.0617763538655| 2294.800000000318| 9.699999999998631| 1.5297600000000104|-0.28951333333333285| -9.069999999999999|1640.1909338193284| 52.61133854675223| 4.389999999999598|-0.00407807269284...|1640.0150599999947| 3.9599999999993774| 1640.042665333331|-0.00842113722681609| 732.1692|-0.03629223969903751|1640.7258800000031| 51.33785857627501| | ACC|2017-03-01| 5.75|49.491400856833565| 262735.0| -6.629444541114854| 1410.721791313194| 1772.0|49.060357133582286|23.565583461932192|-51.454922759071565|1410.7219072727064|0.030574383107037483| 1410.700015151521|1410.7113191410049| 1410.72224176064|1410.7439316326256|1410.6995673826887| 905000.0|49.067182919961205| 8764.0| 1.2998695E7| 1410.733374102587|0.021781761943754866| -4.59234230563| 0.06715151515151513| 107.5999999999848|1410.7391656565708| 6182.920000001178|20.635041040625115|1410.7335396026383|Construction Mate...|1410.7478606060608| 113.19999999998419|0.002078388853745374| 32.23952810598773| -7.657719709791194| 1974855.0| null| 87.69999999999312| 1.0320472959002092| 744.0|1411.8537990570214|1410.2335636363703|0.31816008085405617|0.006325142549770506|0.033079603943277795| 1.48971012E9| 0.04628484848484847|49.451893775661745|49.284111919455306| 34.60999999999269| 0.02966146079979648|1410.7334206060502|1410.7456338578834| 49.20545245222939| 0.01129784199952293| 1409.613042155074| 64559.39999999029|1410.7106351515226| 49.44191573437821| 44.50498166208425|49.434076438691214|1410.7004599986913| 130.90000000001325| 4.4000000000006825|1410.6888337609898|2651.4699999999466| 9.649999999999817| 1.032400000000012| 0.08652121212121208| -10.649999999999288| 1410.691392813318|50.377515924215324| 3.889999999999603|0.001746899554882...| 1410.7430969697| 2.0100000000001685|1410.7334206060502|0.003383191010855039| 848.9078787878788| 0.01085914231898228|1411.2408363636387| 49.44191573437821| | ACC|2017-06-01| 6.0|49.664397997228534| 195854.0| -2.2576310265573243|1626.5471609877225| 1772.0| 49.97953134744658| 23.08819348368837| -50.87753707564445| 1626.547142222199|-0.07271798364638145| 1626.597961111132| 1626.572545516046|1626.5469169447508|1626.5317854065313|1626.5981952859747| 663470.0| 49.98822149469257| 7917.0| 1.2998695E7| 1626.521126252042|-0.05149166430889215| -0.7795018736649904|-0.15268148148148147| 107.5999999999866|1626.5025432098819| 6182.920000001042| 20.71329801124263|1626.5212753417566|Construction Mate...|1626.5120000000077| 112.69999999999119|-0.00440990928621...|31.303759621839838|-3.1769200561980067| 650731.0| null| 87.70000000000195| 1.3259689388497566| 743.0|1628.0596615848128|1625.8583629629688| 0.3551850404492402|-0.01202844048902788|-0.07713326730659954| 1.49739972E9|-0.10267407407407407| 49.32753433595716| 49.77252162154353| 34.60999999999417|-0.07074349473621051|1626.5212355555545| 1626.587599818823| 49.69214786120583| -0.0256416029977074|1624.9828095262715| 64559.39999999343|1626.5726335802442|49.319115321825684|43.194610807836625| 49.31933322149429|1626.5984086090657| 132.0| -0.29999999999996807|1626.6168004403212| 2651.4700000002| 9.599999999998978| 1.32418518518519|-0.20241481481481477| -12.960000000001036| 1626.586951460973| 49.36888192084301| 1.5400000000001588|-0.00306287615195...|1626.5009851851905| -2.120000000000288|1626.5212355555545|-0.00609729731654...| 714.3631111111112|-0.02586309302077...|1627.1482814814829|49.319115321825684| | ACC|2018-04-01| 5.75| 49.93442879580245| 256459.0|-0.05063271753298533|1556.8622979896174| 1174.0|49.864890981709785|22.186021685536574| -50.74757971748986|1556.8623428571423| 0.06988669062941702|1556.8136298412767| 1556.837438438712| 1556.862154407611|1556.7811345581213| 1556.813788686663| 745051.0| 49.86489098170905| 8821.0| 1.3850857E7|1556.8863459554127|0.049686029270295945| 0.39718565756914465| 0.14623492063492066|110.40000000001501|1556.9202243386246| 6514.0|21.155120810578104|1556.8865262267739|Construction Mate...|1556.9220190476199| 117.30000000001547|0.004474678770058258|30.852191176984235|-1.1479141551425753| 223417.0| null| 92.199999999993| 1.333987291408159| 725.0| 1558.525634790635| 1556.279314285709|0.32689392210756335|0.013204018045723662| 0.07405784844012199|1.5238154914285715E9| 0.0969206349206349|48.607318335040794|48.147867997556205| 34.60999999999301| 0.0691111111517113|1556.8864304762358| 1556.810911429879| 49.85152857969748|0.024371819169826035|1555.2472261618443| 66257.80000000831|1556.8380757672026| 48.60727972158535|43.222185460347546|48.610112629452814|1556.8124683783283| 137.09999999998672| 4.5|1556.7811995628072| 2701.109999999697| 9.449999999998598| 1.3308888888888994| 0.19629206349206338| -13.720000000000944|1556.7890667088839| 48.10092011924918| 4.580000000000922|0.003296386054516959|1556.9062730158748| 2.799999999999591|1556.8864304762358|0.006539454253657963| 758.5065396825397|0.024970060379543798|1557.5750857142864| 48.60727972158535| | ACC|2018-08-01| 6.25| 50.38706575458382| 261146.0| 0.2498393992711462|1580.5816133335206| 1174.0| 49.36925044691898|22.181562578629364|-50.434502668160526| 1580.581623492063| 0.09760122653085036|1580.5120482539705|1580.5466354492992| 1580.58148276797|1580.6088913518238|1580.5122933331347| 786003.0| 49.37202475978255| 9208.0| 1.3850857E7|1580.6161374013532| 0.06971864647824513| -1.0009102656017483| 0.20800000000000002|110.40000000001501|1580.6426158730103| 6514.0| 20.83437128170153| 1580.61618135394|Construction Mate...|1580.6429460317493| 120.09999999998432|0.006204851346505887|30.583089567613605| 0.6295501523768352| 1070859.0| null| 92.199999999993| 1.5117301990671868| 700.0| 1582.288240370154| 1579.900692063489|0.40244543105448866|0.018246643646633504| 0.10441723243838501| 1.534434462857143E9| 0.13838095238095238|49.526909214081776| 49.78782767592298| 34.60999999999301| 0.09826393980012844| 1580.616161269909|1580.6309396189783|50.296343873398094| 0.03469858596013988|1578.9440821696412| 66257.80000000831| 1580.547031534389|49.523235459461596|42.657595530343166|49.519384700144435|1580.5117641214968| 140.40000000001075| 4.799999999999943|1580.6751677276893| 2701.109999999697| 9.449999999998598| 1.510488888888888| 0.280025396825396| -17.9199999999995|1580.5148480309679| 49.57688506037863| 3.689999999999523|0.004506642043117355|1580.6438539682563| 0.2899999999999738| 1580.616161269909|0.009015612296383785| 1685.264126984127| 0.03487132780373331|1581.3833015873058|49.523235459461596| | ACC|2019-08-01| 5.149999999999956|49.457850396976774| 178490.0| -4.856461724383827|1529.6869197907665| 1174.0| 49.72517683981136|21.809467126632327| -50.70549709443406|1529.6868810194226|-0.04239464131136942| 1529.71675137056|1529.7019685773244|1529.6869830017047|1529.7521157520275|1529.7162818732331| 798587.0| 49.7174865359533| 8868.0| 1.4427632E7|1529.6723342759929|-0.03011481491216...| -2.0098898835255117|-0.08938361238702033|123.90000000000612|1529.6669630068634| 6689.369999999999| 21.58637448396144|1529.6721939945362|Construction Mate...|1529.6806341680253| 121.5|-0.00274454828636...| 29.95850718236893| -6.404311067507796| 1175301.0| null|108.79999999999326| 2.0282634432210096| 662.0|1531.9543273623872|1528.6733738331545| 0.4624674724980015|-0.00674081080078...|-0.04490382208536666|1.5659967040539339E9|-0.05847532967847093| 49.42792769253955| 49.30386218887929| 25.169999999998605|-0.04241682527407111|1529.6722773744991| 1529.753323433954| 49.44331558143869|-0.01478900717319...| 1527.390227386614| 71468.90000000296|1529.7013493357044| 49.43398325169964| 41.42784297639298| 49.43724927251633|1529.7170978166182| 145.0| -1.399999999999996|1529.7796031081425| 2870.5| 9.40000000000057| 2.027596680989785| -0.1213587198103427| -13.860000000000454| 1529.652170387154| 49.45502558942691| 3.280000000000109|-0.00161784493964...| 1529.660127426287| 2.989999999999828|1529.6722773744991|-0.00319711471179...|2020.7627796710624|-0.01512923929332...|1530.6673877611547| 49.43398325169964| | ACC|2020-09-01| 3.349999999999858| 50.41572128680984| 197555.0| 0.6542087166907428|1363.8120794774136|1869.9499999998718| 49.87697226602822|20.395316031989175| -48.82369654753964|1363.8121349019666| 0.07992086991314654|1363.7540141176405|1363.7838137243752| 1363.812695816937|1363.8355660395175|1363.7532492689443| 28167.0| 49.89265854053613| 8520.0| 1.3280223E7|1363.8419415331086| 0.05743583168403611|-0.14292795813351533| 0.17753725490196084|124.80000000001047|1363.8380915032708| 6186.609999999561|20.773441718560715|1363.8422725129099|Construction Mate...| 1363.86470588235| 122.90000000001412|0.005865179849632459|28.161262644249266| 1.8514406922431785| 1479410.0| null|102.90000000001233| 1.910121871382614| 618.0|1365.9365780860578|1362.8830666666581|0.45088596498814026|0.018042430857728085| 0.08701252765760371|1.6003605670588236E9| 0.12047843137254904| 51.58869712784897|49.687366413542506| 25.170000000001103| 0.07953313227883779|1363.8420407843855| 1363.931150885012| 50.32643845906312|0.029576695973567598|1361.7475034827073| 72937.10000000765|1363.7825448366098| 51.58068955539104| 39.89901988696126| 51.57474056711988|1363.7552599852552| 156.40000000001606| 1.0|1363.9634354933419| 2667.68999999985| 9.0| 1.9097254901960818| 0.22997647058823528| -2.959999999999521| 1363.706099304432| 52.62732878149021| 7.26999999999933|0.004769407197511656|1363.8667843137257| 10.679999999999996|1363.8420407843855|0.009460053072048316| 2356.270274509804|0.028553739119412627|1364.7644235294215| 51.58068955539104| | ACC|2020-11-01| 3.34999999999994| 49.67745341659071| 209476.0| -2.0878145019796452|1687.8596868925567|1869.9499999999105|50.286563004323476| 21.80729228561921|-49.996647020100596| 1687.859513252211|0.062152213127478935|1687.8108143023774| 1687.835151592077| 1687.858558476134|1687.8639382033498|1687.8117066478844| 70977.0| 50.29211948595726| 9245.0| 1.3280223E7|1687.8822070295312|0.045961052419045405| -0.7990055999927165| 0.14099849974995834| 124.8000000000082|1687.9090737345143| 6186.609999999571|21.555727047792512|1687.8817313558761|Construction Mate...|1687.9136856142713| 125.09999999998628|0.004141197735981265|29.651780695642312|-2.7118962332669714| 1906358.0| null|102.90000000001164| 2.1182326498914246| 618.0|1690.2102253130715|1686.8634355725915|0.44744196963923116|0.012289414383075915| 0.0691339321581224|1.6054323037306218E9| 0.09069011501916985| 49.6665002878012| 48.15103462756178| 25.17000000000037| 0.06946061529157155|1687.8820720120782| 1687.721264628014| 49.72522627869896|0.023172879739076988|1685.5539187110671| 72937.10000000663|1687.8355570372898|49.669872662020154|41.243396499962074| 49.67211686290141|1687.8125974237123| 158.90000000001575| -1.600000000000181|1687.6255600996953|2667.6899999998463| 8.800000000000786| 2.126746124354073| 0.1920320053342224| -10.190000000000774| 1687.822125312153| 48.82972767325069| 6.930000000000255|0.002801203038031...|1687.8983580596769| 9.5|1687.8820720120782|0.005823024498459208| 4996.427737956326|0.022554168353800464|1688.9654275712667|49.669872662020154| | ACC|2021-01-01| 3.3499999999999344|50.497384292821565| 190183.0| 2.3612300151144594|1698.1440230068329|1869.9499999999107| 49.86365158212937|20.905459769156547|-49.910049002325124|1698.1441819607887|-0.01288237879627...| 1698.158566274501| 1698.15053777044|1698.1441253493726| 1698.009377443505| 1698.158641996235| 83822.0|49.869009883997826| 10000.0| 1.4536732E7|1698.1367850840345|-0.01149636280124...| 1.7420574880737019|-0.04305098039215...|140.30000000001206| 1698.145369934636| 6675.3500000007225| 21.12688199753174|1698.1371201052452|Construction Mate...|1698.1426509803919| 126.5|-0.00123811324021...|29.517369338765544| 2.965578374620014| 2517318.0| null| 105.5| 2.039335590841497| 619.1499999999406|1700.3974848914368|1697.1499294117627| 0.4520843880788953|-0.00241695057000...|-0.01850160692868742|1.6107031552941177E9|-0.02789019607843...| 49.53953532222643| 50.62859537470962| 25.17000000000044|-0.02008039209602...| 1698.136854902029|1697.7925522853618|50.515855807183804|-0.00700524412743...| 1695.876224912606| 76598.0|1698.1514420915012| 49.53642957873037| 41.6356557089261|49.532412882229316|1698.1556217121738| 156.30000000001536| -0.5999999999999422| 1697.701725125349| 3173.400000000043| 8.800000000000761| 2.042933333333346|-0.05642352941176539| -14.49000000000077|1698.2087023742165| 48.55272377319887| 4.060000000000305|-5.29988587315213...|1698.1319843137276| 1.8899999999999408| 1698.136854902029|-0.00117525332895...|3264.5592156862745|-0.00508394173518...| 1699.15419607843| 49.53642957873037| | ACC|2021-02-01| 3.3500000000002212| 50.05259567742876| 190484.0| -3.202449110511591|1758.4652789877744|1869.9500000001099| 49.83255506851524|20.911455117813286| -50.10359802724794| 1758.465274224288| 0.12221822747282025| 1758.379644801297|1758.4209001415181|1758.4639922613558|1758.5723327500243|1758.3814236401097| 99640.0|49.833202254531415| 9100.0| 1.4536732E7| 1758.505833891437| 0.08678272752120088| -2.210629071998468| 0.2527830702231895|140.29999999999836| 1758.535687715482| 6675.3500000002905| 21.06889906170169|1758.5056624992853|Construction Mate...|1758.5222645617835| 128.09999999999198|0.007449026478986...|29.193645521174712|-3.6180213786840807| 2818643.0| null| 105.5| 2.55647109476426| 618.5900000000031|1761.3185886268896|1757.2707947740837| 0.467596116807699|0.022067180525954033| 0.12845296544906903| 1.613243363565596E9| 0.1617378878606424| 49.63274565878134| 48.89968676946759| 25.169999999998574| 0.12376422859675727| 1758.50582335335| 1758.507171045154| 49.91646020639196| 0.04167023792786816|1755.6930580798173| 76598.0|1758.4236885320295| 49.63662512220892| 40.82458109625625| 49.63808691641429|1758.3772095338306| 156.5999999999892| -3.200000000000131|1758.5790583630821|3173.4000000002147| 8.799999999999905| 2.558662221012528| 0.34760479041916326| -12.620000000000532|1758.4015458263584| 49.72950847430849| 5.030000000000006| 0.00517266674686123|1758.5379286880782| 3.870000000000204| 1758.50582335335| 0.01034843245822826|3420.7524496461624| 0.04369060768542613|1759.7983396842747| 49.63662512220892| | ACC|2022-01-01| 3.349999999999661|50.561670738080096| 162601.0| 2.440858571278567| 2257.002591333603| 895.1500000000988| 49.13409609805948|20.671767513836123|-48.629553145423294|2257.0024526666684| 0.06074588307913758| 2256.960738333323|2256.9810008073405|2257.0020449658814|2256.9441612461883| 2256.960951029923| 201546.0| 49.13795935590224| 10800.0| 1.513278E7| 2257.022646325125| 0.04261188014672165|-0.36329890229128053| 0.12371999999999989|143.80000000001993|2257.0035422222268| 6700.0| 21.41246233385628| 2257.022557127831|Construction Mate...| 2257.041186666665| 143.80000000001993|0.002618630560759795|29.156921050018212| 3.374233664143609| 2809384.0| null|133.69999999998208| 2.6808472709738886| 604.2000000000849| 2259.795033640589| 2255.662126666683| 0.4116002691765086| 0.00822941755657787| 0.06312404210114134| 1.64231412E9| 0.08149999999999988| 51.30137779267431| 50.75449036391336| 34.940000000001966| 0.05867435903773312|2257.0226306666536| 2256.943533041759| 50.49820189882675|0.020512161954419694| 2254.250227692723| 80000.0|2256.9816279999955| 51.30274513187338|41.242767909270256| 51.30252099571606|2256.9594330857267| 165.69999999997657| 2.0|2256.8304530529726| 3469.0| 8.80000000000121| 2.6805466666666518| 0.16564666666666658| -17.41999999999848|2257.0127069374016| 52.24870892242551| 6.010000000000043|0.001960677962589...|2257.0384466666665| 5.430000000000254|2257.0226306666536|0.004041790622388502|1049.6338666666666|0.021567721600874695| 2258.310053333323| 51.30274513187338| | ACC|2022-09-01| 3.3499999999995596|50.198970446950575| 186867.0| -0.802952065717992|2473.0404393942063| 895.1500000001237|49.436307193751304|21.199887338766278|-51.126433268831825| 2473.040543636374| 0.09869544649446305|2472.9683663636397| 2473.00526656844|2473.0410293859736| 2473.185949588434|2472.9678844624896| 606891.0| 49.43391051385238| 9900.0| 1.513278E7| 2473.077374401596| 0.07106930342244562| -0.7290161999279634| 0.21855151515151452|143.80000000002235|2473.0876000000017| 6700.0| 21.20956231272943| 2473.077449751784|Construction Mate...|2473.0928666666664| 152.1000000000016| 0.00423547060238682| 28.83992703670157|-0.5278288941444729| 1823597.0| null| 133.6999999999795| 4.5341610044734955| 685.9800000001029|2477.9756962994006|2470.8361272727543|0.48921491903429154|0.013980856816909623| 0.10748966923738705|1.6633348472727273E9| 0.14650909090909017| 49.30751078128637| 50.57262653679213| 34.940000000001994| 0.09946526813022424| 2473.077347878796| 2473.210722704382| 50.11765454971323| 0.03642036581494143|2468.1789994581723| 80000.0| 2473.004157575708| 49.30624104725494| 40.51449704173318| 49.30060806679288| 2472.969960082543| 175.3000000000267| 3.0999999999996417|2473.3346134891467| 3469.0| 8.800000000001365| 4.536921212121182| 0.2861575757575757| -25.70999999999863|2472.8681209193173| 50.01751418956178| 7.410000000001548|0.003637557445317303|2473.1070060606075| 8.599999999999495| 2473.077347878796|0.007152304042787357| 4826.811636363636|0.035306485898695346| 2475.319666666644| 49.30624104725494| |ADANIENT|2015-05-01| 6.5|50.704353904453214| 219798.0| 1.7418483674718064| 741.9407728104702| 590.6549176470585| 50.17968063278244|22.788314258686363| -47.59728243993599| 741.940436862746| 0.03293733262936284| 741.91175764706| 741.926899726394| 741.9400828482698| 741.9537801402535| 741.9119280568515| 509000.0|50.169422104120265| 7708.0| 1.1234571E7| 741.9541020078095|0.025285995096065224| 1.4598326543065452| 0.08397647058823615|108.59999999998709| 741.9585516339841| 5464.390000000417| 21.16407411757601| 741.9536485875716| Metals & Mining| 741.9671372549026| 111.40000000001316|0.005715588818220529|29.784021565656676| 3.0542508481467134| 262831.0| null| 99.80000000000142| 1.1745812208130089| 766.0| 743.2113411449169| 741.3828705882312|0.48867998438314963|0.018057742108649046|0.038851734398854844|1.4319415552941177E9| 0.05457254901960731| 52.13471246219177|51.009999160079225| 34.60999999999465| 0.03937828054692422| 741.9540188235299| 741.8902024225351| 50.63325831153231|0.013565739302789624| 740.6966965021393| 51295.5| 741.9260538562105| 52.1451068426361| 40.79242417991894| 52.15711235868314| 741.9147968531719| 121.59999999998598| 1.7999999999998837| 741.8424155337871| 2103.58999999988| 10.0| 1.1749254901960802| 0.11051764705882366| -10.4100000000017| 741.9396474846357| 51.29859413030272| 5.008999999999129|0.004140253308034688| 741.9635450980405| 4.7999999999995016| 741.9540188235299|0.008916785011885527| 7641.484862745098|0.012102873224330877| 742.5292392156903| 52.1451068426361| |ADANIENT|2015-07-01| 6.25|49.336555024531044| 229414.0| -6.405990381373355| 87.48794290290027| 803.9000000000675| 49.68354274987704| 22.27000656052686|-52.479843133958774| 87.48793489861272|-0.00348003969334...| 87.49048926093948| 87.48916124455387| 87.48788953111803| 87.5082069415727| 87.4905394995302| 628000.0| 49.68842891870525| 7668.0| 1.1234571E7| 87.48658104456273|-0.00251841472342...| -3.586757935269739|-0.00783084311632...|108.59999999998554| 87.48610814656706| 5464.390000000444| 20.00714163012356| 87.48657060081415| Metals & Mining| 87.48523212379922| 111.09999999998534|-0.00388424578980...|30.155370471040268| -7.216921032584023| 600979.0| null| 99.80000000000817|0.14656362527199596| 754.0| 87.62866487575235| 87.41443436499438| 0.2945924045602651|-0.01023864992063...|-0.00383734502731...|1.4371096500800426E9|-0.00537620064034...| 48.27333048684419|47.179869172278885| 34.60999999999336|-0.00346496763484...| 87.48658217716088| 87.52749857461389|49.344017292098414|-0.00131893030388...| 87.34449947856827| 51295.5| 87.4892431519032| 48.27421985006039|41.616470489019044| 48.27577623568775| 87.4904079458411| 123.59999999998446| 1.2999999999999918| 87.54162193482804| 2103.589999999876| 10.0|0.14673159018143025|-0.01004535752401...| -12.809999999998661| 87.48779585623599|49.250489807655896| 3.690999999999718|-0.00278709101060...| 87.48547892209139| 2.149999999999745| 87.48658217716088|-0.00550110434500...| 9651.129935965848|-0.00124670128762...| 87.55841115261474| 48.27421985006039| |ADANIENT|2016-01-01| 5.75|49.180864004478714| 215995.0| -3.712777634597596| 78.08159267976092| 803.9000000000138|49.876377061984655|24.210153227142957| -50.39046819190358| 78.08158509803955|-0.01168271232583...| 78.08985647058863| 78.08574441147813| 78.0815961491732| 78.07682850238454| 78.08984648773638| 845000.0| 49.86546433076319| 7501.0| 1.2163619E7| 78.0774876639353|-0.00831543723436...| -2.7950491270643143|-0.02483137254901...| 104.0| 78.07359738562117| 5851.390000000429|21.077972860504932| 78.07746076788818| Metals & Mining| 78.0774509803923| 108.0|-0.01484360449424...|31.242945918589957| -5.357715467238324| 1436340.0| null| 84.5|0.20264813787732647| 730.0| 78.30277833173713| 77.97503529411719| 0.3502525529527759|-0.03753392736382352|-0.01245081851940...|1.4528706847058823E9|-0.01652549019607...| 50.17915367584198| 48.45207448867783| 34.60999999999465|-0.01160708898421...| 78.07747607843326| 78.10479758606562| 49.12794060924538|-0.00413538128504...| 77.8521738251291| 56637.60000000299| 78.08571921568623| 50.18583486683896|42.024362746215544|50.186839120391966| 78.08991158640755| 126.30000000001088| 4.0| 78.11156112803293|2294.8000000002426| 9.699999999998905|0.20263529411764533|-0.03319215686274505| -7.669999999998862| 78.09287364675433| 50.84834071225887| 5.689999999999709|-0.00936066217697...| 78.07410980392167| 6.849999999999187| 78.07747607843326|-0.01874007622952373|10427.515294117647|-0.00416717492936...| 78.1716470588238| 50.18583486683896| +--------+----------+----------------------+------------------+----------------------+--------------------+------------------+------------------+------------------+------------------+-------------------+------------------+--------------------+------------------+------------------+------------------+------------------+------------------+---------------------+------------------+---------------------+---------------------------+------------------+--------------------+--------------------+--------------------+------------------+------------------+-----------------------+------------------+------------------+--------------------+------------------+--------------------+--------------------+------------------+-------------------+-----------------------+------------+------------------+-------------------+-------------------------+------------------+------------------+-------------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+------------------------+--------------------+------------------+------------------+------------------+--------------------+------------------+-------------------------+------------------+------------------+------------------+------------------+------------------+-------------------------+--------------------------+------------------+------------------+----------------------+-------------------+--------------------+---------------------+------------------+------------------+--------------------+--------------------+------------------+-------------------+------------------+--------------------+------------------+--------------------+------------------+------------------+ only showing top 20 rows
monthly_df = monthly_df.toPandas()
monthly_df
| company | month | avg(Reverse Repo Rate) | avg(RSI14) | avg(Car Registrations) | avg(CCI10) | avg(TRIMA10) | avg(SAR) | avg(fastdsr) | avg(ADX20) | ... | avg(Inflation Rate ) | avg(ROC5) | avg(close) | avg(Food Inflation) | avg(middleband) | avg(ROC10) | avg(volume) | avg(macd1520) | avg(high) | avg(slowk) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | ACC | 2015-02-01 | 6.75 | 51.341733 | 225854.0 | 7.375884 | 1587.842455 | 1510.245415 | 50.706838 | 20.462642 | ... | 5.370 | 0.005904 | 1587.924620 | 6.88 | 1587.888181 | 0.011817 | 1479.123302 | 0.043773 | 1588.835122 | 51.792747 |
| 1 | ACC | 2015-03-01 | 6.50 | 49.260220 | 230638.0 | -2.859966 | 1607.493176 | 1481.700000 | 49.450973 | 20.107720 | ... | 5.254 | -0.005406 | 1607.410496 | 6.14 | 1607.447115 | -0.010778 | 835.136073 | -0.042587 | 1608.200030 | 48.195062 |
| 2 | ACC | 2015-04-01 | 6.50 | 49.165089 | 217437.0 | -4.717163 | 1562.538397 | 1527.603356 | 49.967016 | 22.761690 | ... | 4.865 | -0.005327 | 1562.463074 | 5.11 | 1562.496368 | -0.010869 | 944.018818 | -0.044448 | 1563.427103 | 48.229864 |
| 3 | ACC | 2015-06-01 | 6.25 | 50.061280 | 212847.0 | -0.000776 | 1429.092497 | 1772.000000 | 47.988246 | 23.702557 | ... | 5.398 | -0.002823 | 1429.052600 | 5.48 | 1429.070272 | -0.005681 | 749.572606 | -0.021142 | 1429.813770 | 46.109067 |
| 4 | ACC | 2015-11-01 | 5.75 | 49.697334 | 228267.0 | -1.328305 | 1358.833029 | 1772.000000 | 49.597447 | 23.574441 | ... | 5.412 | -0.001917 | 1358.807763 | 6.07 | 1358.818412 | -0.004099 | 561.771408 | -0.015752 | 1359.409513 | 49.098518 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 8888 | UPL | 2022-04-01 | 3.35 | 50.282861 | 153995.0 | -0.097747 | 808.763722 | 239.400000 | 49.774696 | 20.623994 | ... | 7.790 | 0.005578 | 808.802304 | 8.38 | 808.784862 | 0.010913 | 7372.943852 | 0.019620 | 809.220267 | 51.261911 |
| 8889 | VEDL | 2016-11-01 | 5.75 | 50.778532 | 235977.0 | 0.135301 | 217.227011 | 120.392108 | 49.856638 | 22.728598 | ... | 3.630 | 0.008993 | 217.242660 | 2.03 | 217.235665 | 0.017875 | 45558.532825 | 0.008557 | 217.445962 | 50.753514 |
| 8890 | VEDL | 2021-12-01 | 3.35 | 49.799167 | 159360.0 | -1.850097 | 343.598404 | 58.250000 | 49.276777 | 22.684132 | ... | 5.660 | 0.001069 | 343.600456 | 4.05 | 343.599599 | 0.002018 | 24355.279197 | 0.000605 | 343.820733 | 49.835236 |
| 8891 | WIPRO | 2021-02-01 | 3.35 | 48.974485 | 190484.0 | -6.424699 | 430.330181 | 153.420000 | 49.850835 | 21.081574 | ... | 5.030 | -0.001331 | 430.324439 | 3.87 | 430.327254 | -0.002295 | 30572.976459 | -0.002715 | 430.588386 | 50.576229 |
| 8892 | WIPRO | 2022-06-01 | 3.35 | 48.766103 | 179880.0 | -4.586601 | 445.847586 | 153.420000 | 48.893408 | 22.128834 | ... | 7.010 | -0.009633 | 445.807537 | 7.75 | 445.825520 | -0.019279 | 19548.810667 | -0.021775 | 446.039993 | 49.418171 |
8893 rows × 84 columns
monthly_df.columns
Index(['company', 'month', 'avg(Reverse Repo Rate)', 'avg(RSI14)',
'avg(Car Registrations)', 'avg(CCI10)', 'avg(TRIMA10)', 'avg(SAR)',
'avg(fastdsr)', 'avg(ADX20)', 'avg(WILLR)', 'avg(sma10)',
'avg(macd1226)', 'avg(sma20)', 'avg(ema15)', 'avg(ema10)',
'avg(KAMA10)', 'avg(TRIMA20)', 'avg(Tourist Arrivals)', 'avg(fastksr)',
'avg(Steel Production)', 'avg(Gross National Product)', 'avg(TRIMA5)',
'avg(macd1020)', 'avg(CCI5)', 'avg(MOM15)', 'avg(Export Prices)',
'avg(TYPPRICE)', 'avg(GDP per Capita PPP)', 'avg(HT_DCPERIOD)',
'avg(ema5)', 'first(sector)', 'avg(open)', 'avg(Producer Prices)',
'avg(PPO)', 'avg(ADX10)', 'avg(CCI15)', 'avg(Fiscal Expenditure)',
'avg(company)', 'avg(Import Prices)', 'avg(ATR)',
'avg(Crude Oil Production)', 'avg(upperband)', 'avg(low)', 'avg(BETA)',
'avg(ROC20)', 'avg(macd520)', 'avg(date)', 'avg(MOM10)', 'avg(fastk)',
'avg(MFI)', 'avg(Corporate Tax Rate )', 'avg(APO)', 'avg(sma5)',
'avg(KAMA20)', 'avg(RSI8)', 'avg(macd510)', 'avg(lowerband)',
'avg(Military Expenditure)', 'avg(sma15)', 'avg(fastd)', 'avg(ADX5)',
'avg(slowd)', 'avg(ema20)', 'avg(Consumer Price Index)',
'avg(Industrial Production)', 'avg(KAMA30)', 'avg(GDP)',
'avg(Bank Lending Rate)', 'avg(Trange)', 'avg(MOM20)',
'avg(Balance of Trade)', 'avg(HT_TRENDLINE)', 'avg(ULTOSC)',
'avg(Inflation Rate )', 'avg(ROC5)', 'avg(close)',
'avg(Food Inflation)', 'avg(middleband)', 'avg(ROC10)', 'avg(volume)',
'avg(macd1520)', 'avg(high)', 'avg(slowk)'],
dtype='object')
# Plot for ADANIGREEN
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
adanigreen = monthly_df[monthly_df['company'] == 'ADANIGREEN'].set_index('month').sort_index()
ax1[0].plot(adanigreen['avg(close)'])
ax1[0].set_title('ADANIGREEN Close', fontstyle='italic')
ax1[1].plot(adanigreen['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')
ax2[0].plot(adanigreen['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')
ax2[1].plot(adanigreen['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')
ax3[0].plot(adanigreen['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')
ax3[1].plot(adanigreen['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')
ax4[0].plot(adanigreen['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')
ax4[1].plot(adanigreen['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')
fig.tight_layout()
# Plot for ADANIENT
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
adanient = monthly_df[monthly_df['company'] == 'ADANIENT'].set_index('month').sort_index()
ax1[0].plot(adanient['avg(close)'])
ax1[0].set_title('ADANIENT Close', fontstyle='italic')
ax1[1].plot(adanient['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')
ax2[0].plot(adanient['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')
ax2[1].plot(adanient['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')
ax3[0].plot(adanient['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')
ax3[1].plot(adanient['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')
ax4[0].plot(adanient['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')
ax4[1].plot(adanient['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')
fig.tight_layout()
# Plot for BAJAJFINANCE
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
bajfin = monthly_df[monthly_df['company'] == 'BAJFINANCE'].set_index('month').sort_index()
ax1[0].plot(bajfin['avg(close)'])
ax1[0].set_title('BAJFINANCE Close', fontstyle='italic')
ax1[1].plot(bajfin['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')
ax2[0].plot(bajfin['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')
ax2[1].plot(bajfin['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')
ax3[0].plot(bajfin['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')
ax3[1].plot(bajfin['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')
ax4[0].plot(bajfin['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')
ax4[1].plot(bajfin['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')
fig.tight_layout()
# Plot for RELIANCE
fig, (ax1, ax2, ax3, ax4) = plt.subplots(4, 2)
fig.set_figheight(10)
fig.set_figwidth(20)
reliance = monthly_df[monthly_df['company'] == 'RELIANCE'].set_index('month').sort_index()
ax1[0].plot(reliance['avg(close)'])
ax1[0].set_title('RELIANCE Close', fontstyle='italic')
ax1[1].plot(reliance['avg(Inflation Rate )'])
ax1[1].set_title('Inflation Rate', fontstyle='italic')
ax2[0].plot(reliance['avg(Industrial Production)'])
ax2[0].set_title('Industrial Production', fontstyle='italic')
ax2[1].plot(reliance['avg(Consumer Price Index)'])
ax2[1].set_title('Consumer Price Index', fontstyle='italic')
ax3[0].plot(reliance['avg(GDP)'])
ax3[0].set_title('GDP', fontstyle='italic')
ax3[1].plot(reliance['avg(Bank Lending Rate)'])
ax3[1].set_title('Bank Lending Rate', fontstyle='italic')
ax4[0].plot(reliance['avg(Food Inflation)'])
ax4[0].set_title('Food Inflation', fontstyle='italic')
ax4[1].plot(reliance['avg(Steel Production)'])
ax4[1].set_title('Steel Production', fontstyle='italic')
fig.tight_layout()
indicator_columns = ['Inflation Rate ', 'Balance of Trade', 'Bank Lending Rate', 'Car Registrations', 'Consumer Price Index', 'Crude Oil Production', 'Fiscal Expenditure', 'Industrial Production', 'Food Inflation', 'Producer Prices', 'Reverse Repo Rate', 'Steel Production', 'Tourist Arrivals', 'Corporate Tax Rate ', 'Export Prices', 'GDP per Capita PPP', 'GDP', 'Gross National Product', 'Import Prices', 'Military Expenditure']
close_plus_indicators = []
for col in (['close'] + indicator_columns):
close_plus_indicators.append('avg({})'.format(col))
adanient_essential = adanient[close_plus_indicators]
adanigreen_essential = adanigreen[close_plus_indicators]
reliance_essential = reliance[close_plus_indicators]
bajfin_essential = bajfin[close_plus_indicators]
plt.figure(figsize=(12,12))
sns.heatmap(adanient_essential.corr(), cmap='coolwarm', annot=True)
<AxesSubplot:>
plt.figure(figsize=(12,12))
sns.heatmap(adanigreen_essential.corr(), cmap='coolwarm', annot=True)
<AxesSubplot:>
plt.figure(figsize=(12,12))
sns.heatmap(bajfin_essential.corr(), cmap='coolwarm', annot=True)
<AxesSubplot:>
plt.figure(figsize=(12,12))
sns.heatmap(reliance_essential.corr(), cmap='coolwarm', annot=True)
<AxesSubplot:>
exprs = {x: "avg" for x in cols}
for indicator in indicator_columns:
exprs[indicator] = 'first'
exprs["company"] = "first"
sector_df = df.groupby(df.sector, df.month).agg(exprs).toPandas()
sector_df
| sector | month | first(Reverse Repo Rate) | avg(RSI14) | first(Car Registrations) | avg(CCI10) | avg(TRIMA10) | avg(SAR) | avg(fastdsr) | avg(ADX20) | ... | first(Inflation Rate ) | avg(ROC5) | avg(close) | first(Food Inflation) | avg(middleband) | avg(ROC10) | avg(volume) | avg(macd1520) | avg(high) | avg(slowk) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Automobile and Auto Components | 2015-08-01 | 6.25 | 49.662731 | 224700 | -1.998022 | 5337.720340 | 5529.659650 | 50.003184 | 21.683560 | ... | 3.741 | -0.004738 | 5337.516660 | 2.20 | 5337.607947 | -0.009484 | 4880.420590 | -0.102422 | 5340.834713 | 49.984793 |
| 1 | Automobile and Auto Components | 2015-12-01 | 5.75 | 49.945582 | 236761 | -0.504177 | 4401.618016 | 5554.567799 | 49.985572 | 22.463151 | ... | 5.611 | -0.000729 | 4401.618949 | 6.40 | 4401.620745 | -0.001406 | 3841.525978 | -0.000174 | 4403.275623 | 50.178142 |
| 2 | Automobile and Auto Components | 2017-04-01 | 6.00 | 50.197289 | 254290 | -0.486540 | 5541.569830 | 5414.435714 | 49.927982 | 23.755683 | ... | 2.990 | 0.002254 | 5541.670408 | 0.61 | 5541.626588 | 0.004538 | 3677.465905 | 0.057779 | 5543.670202 | 50.286948 |
| 3 | Automobile and Auto Components | 2018-03-01 | 5.75 | 49.606189 | 263487 | -1.560610 | 5273.128254 | 5414.435714 | 50.170437 | 23.622784 | ... | 4.280 | -0.002404 | 5273.045940 | 2.81 | 5273.079706 | -0.004956 | 4925.275733 | -0.059460 | 5275.285472 | 49.709954 |
| 4 | Automobile and Auto Components | 2018-04-01 | 5.75 | 50.389887 | 256459 | -0.216702 | 5632.311644 | 5414.435714 | 49.904657 | 23.615024 | ... | 4.580 | 0.004572 | 5632.477104 | 2.80 | 5632.405683 | 0.009271 | 5768.151147 | 0.099991 | 5634.661498 | 50.852952 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 1764 | Services | 2021-08-01 | 3.35 | 50.593187 | 151757 | 1.040625 | 1187.519193 | 426.347561 | 48.673977 | 21.311919 | ... | 5.300 | 0.008594 | 1187.616254 | 3.11 | 1187.573210 | 0.017200 | 13620.365659 | 0.051716 | 1188.353961 | 48.792737 |
| 1765 | Telecommunication | 2019-04-01 | 5.75 | 49.604986 | 218679 | -0.497951 | 319.180610 | 518.425000 | 51.265332 | 22.292561 | ... | 2.990 | -0.007952 | 319.158373 | 1.10 | 319.168168 | -0.016025 | 18371.854275 | -0.012474 | 319.378039 | 51.108816 |
| 1766 | Telecommunication | 2019-11-01 | 4.90 | 50.895531 | 210687 | 1.773356 | 311.905260 | 518.425000 | 48.967278 | 22.526909 | ... | 5.540 | 0.018855 | 311.950690 | 10.01 | 311.930547 | 0.038016 | 54653.968067 | 0.025804 | 312.218040 | 50.508269 |
| 1767 | Telecommunication | 2020-08-01 | 3.35 | 49.600241 | 157761 | -3.845947 | 361.803809 | 385.775000 | 49.175487 | 22.741805 | ... | 6.690 | -0.001361 | 361.793421 | 9.05 | 361.798031 | -0.002578 | 42685.908421 | -0.005396 | 362.007100 | 48.872732 |
| 1768 | Telecommunication | 2021-03-01 | 3.35 | 49.213446 | 194780 | -3.307220 | 386.009998 | 385.775000 | 49.544487 | 22.111032 | ... | 5.520 | -0.003927 | 385.993700 | 4.94 | 386.000968 | -0.007858 | 35912.109185 | -0.009589 | 386.258626 | 49.265391 |
1769 rows × 84 columns
indicators2 = []
for col in indicator_columns:
indicators2.append('first({})'.format(col))
fig, ax = plt.subplots(7, 3)
fig.set_figheight(20)
fig.set_figwidth(15)
sectors = list(sector_df['sector'].unique())
num_plots = 0
for i in range(13):
for j in range(3):
try:
sector = sector_df[sector_df['sector'] == sectors[3*i + j]].set_index('month').sort_index()
ax[i][j].plot(sector['avg(close)'])
ax[i][j].set_title(sectors[3*i + j], fontstyle='italic')
except IndexError:
pass
plt.tight_layout()
# Indicators Plot
fig, ax = plt.subplots(7, 3)
fig.set_figheight(20)
fig.set_figwidth(15)
sector = sector_df[sector_df['sector'] == 'Power'].set_index('month').sort_index()
for i in range(7):
for j in range(3):
try:
ax[i][j].plot(sector[indicators2[3*i+j]])
ax[i][j].set_title(indicator_columns[3*i+j], fontstyle='italic')
except IndexError:
pass
plt.tight_layout()
sectors
['Automobile and Auto Components', 'Capital Goods', 'Chemicals', 'Construction', 'Construction Materials', 'Consumer Durables', 'Consumer Services', 'Fast Moving Consumer Goods', 'Financial Services', 'Healthcare', 'Information Technology', 'Metals & Mining', 'NIFTY 50', 'NIFTY BANK', 'Oil Gas & Consumable Fuels', 'Power', 'Realty', 'Services', 'Telecommunication']
# Power Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Power'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
# Financial Services Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Financial Services'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
# Oil Gas & Consumable Fuels Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Oil Gas & Consumable Fuels'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()
# Services Sector Correlation Map
plt.figure(figsize=(12, 12))
sector_essential = sector_df[sector_df['sector']=='Services'][indicators2 + ['avg(close)']]
sns.heatmap(sector_essential.corr(), cmap='coolwarm', annot=True)
plt.tight_layout()